package com.otitan.la.forest.industry.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.otitan.la.forest.industry.constant.DicEnum;
import com.otitan.la.forest.industry.constant.DicTypeEnum;
import com.otitan.la.forest.industry.constant.StaticConstant;
import com.otitan.la.forest.industry.dto.OutputStatisticalDTO;
import com.otitan.la.forest.industry.entity.OutputStatisticalEntity;
import com.otitan.la.forest.industry.vo.OutputStatisticalVO;
import com.otitan.webapp.framework.basepro.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static com.otitan.la.forest.industry.constant.StaticConstant.FILE_EXTENSION_XLS;

/**
 * @Description: 导入导出工具类
 * @Author: wangqingsong
 * @CreateDate: 2020/10/16 11:30
 */
@Slf4j
public class ImOrExportUtil {

    /**
     * zip格式 zip
     */
    private static final String ZIP_FILE_SUFFIX = ".zip";

    /**
     * @return java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity>
     * @throws [redisTemplate, redisKeyPrefix]
     * @Description:    设置excel模板内容
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/19 17:11
     */
    public static List<ExcelExportEntity> enterpriseExport(StringRedisTemplate redisTemplate, String redisKeyPrefix) {

        Map<String, String> entertypeMap = ImOrExportUtil.getDicName(DicTypeEnum.ENTERPRISE_TYPE.getValue(), redisTemplate, redisKeyPrefix);
        Map<String, String> areaMap = ImOrExportUtil.getDicName(DicTypeEnum.AREA.getValue(), redisTemplate, redisKeyPrefix);
//        Map<String, String> managementMap = ImOrExportUtil.getDicName(DicEnum.MANAGEMENT_TYPES.getValue(), redisTemplate, redisKeyPrefix);

        // 每个ExcelExportEntity存放Map行数据的key
        List<ExcelExportEntity> keyList = new ArrayList<>();

        ExcelExportEntity name = new ExcelExportEntity("企业名称*", "name");
        name.setWidth(20);
        keyList.add(name);

        ExcelExportEntity areaCode = new ExcelExportEntity("所在地区*", "areaCode");
        String[] areaArray = mapToStringArray(areaMap);
        areaCode.setReplace(areaArray);
        areaCode.setWidth(20);
        keyList.add(areaCode);

        ExcelExportEntity enterNature = new ExcelExportEntity("企业性质", "enterNature");
        enterNature.setWidth(20);
        keyList.add(enterNature);

        ExcelExportEntity enterType = new ExcelExportEntity("企业类型*", "enterType");
        String[] array = mapToStringArray(entertypeMap);
        enterType.setReplace(array);
        enterType.setWidth(30);
        keyList.add(enterType);

        ExcelExportEntity code = new ExcelExportEntity("统一社会信用代码", "code");
        code.setWidth(20);
        keyList.add(code);

        ExcelExportEntity legalRepresentative = new ExcelExportEntity("法定代表人*", "legalRepresentative");
        legalRepresentative.setWidth(20);
        keyList.add(legalRepresentative);

        ExcelExportEntity tel = new ExcelExportEntity("法定代表人联系电话", "tel");
        tel.setWidth(20);
        keyList.add(tel);

        ExcelExportEntity foundDate = new ExcelExportEntity("成立时间", "foundDate");
        foundDate.setWidth(20);
        foundDate.setFormat("yyyy/MM/dd HH:mm:ss");
        keyList.add(foundDate);

        ExcelExportEntity address = new ExcelExportEntity("地址*", "address");
        address.setWidth(20);
        keyList.add(address);

        ExcelExportEntity lon = new ExcelExportEntity("经度", "lon");
        lon.setWidth(20);
        keyList.add(lon);

        ExcelExportEntity lat = new ExcelExportEntity("经度", "lat");
        lat.setWidth(20);
        keyList.add(lat);

        ExcelExportEntity isLeadingEnterprises = new ExcelExportEntity("是否龙头企业*", "isLeadingEnterprises");
        isLeadingEnterprises.setWidth(20);
        isLeadingEnterprises.setReplace(new String[]{"是_1", "否_0"});
        keyList.add(isLeadingEnterprises);

        ExcelExportEntity scopeBusiness = new ExcelExportEntity("经营范围", "scopeBusiness");
        scopeBusiness.setWidth(20);
        keyList.add(scopeBusiness);

        ExcelExportEntity productStatus = new ExcelExportEntity("生产情况", "productStatus");
        productStatus.setWidth(20);
        keyList.add(productStatus);

        ExcelExportEntity remark = new ExcelExportEntity("企业介绍", "remark");
        remark.setWidth(20);
        keyList.add(remark);

        return keyList;
    }

    /**
     * @return java.lang.String[]
     * @throws [map]
     * @Description:    map转String数组
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 8:57
     */
    public static String[] mapToStringArray(Map<String, String> map) {
        List<String> list = new ArrayList<>();
        for (Map.Entry<String, String> stringEntry : map.entrySet()) {
            String str = stringEntry.getValue() + "_" + stringEntry.getKey();
            list.add(str);
        }
        String[] array = list.toArray(new String[list.size()]);
        return array;
    }

    /**
     * @return java.util.Map
     * @throws [typeKey, redisTemplate, redisKeyPrefix]
     * @Description:    从redis中拿到字典项
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 8:57
     */
    public static Map getDicName(String typeKey, RedisTemplate redisTemplate, String redisKeyPrefix) {
        if (typeKey.equals("AREA")) {
            Map areaMap = redisTemplate.opsForHash().entries(redisKeyPrefix + typeKey + ":");
            return areaMap == null ? null : areaMap;
        }
        Map map = redisTemplate.opsForHash().entries(redisKeyPrefix + StaticConstant.DICTIONARY + typeKey);
        return map == null ? null : map;
    }

    /**
     * @return java.lang.String
     * @throws [basePath, list]
     * @Description:    保存excel到本地
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 9:00
     */
    public static String saveExcelFile(String basePath, List<OutputStatisticalVO> list) {
        String filePath = null;
        if (CollUtil.isNotEmpty(list)) {
            OutputStatisticalVO dto = list.get(0);
            String areaStr = StrUtil.isNotEmpty(dto.getAreaName()) ? dto.getAreaName() : "";

//            TemplateExportParams templateUrl = new TemplateExportParams("/excel/六安林业产值导出模板.xls");

            File file = null;
            try {
                file = ResourceUtils.getFile(StaticConstant.EXPORT_MODEL_ADDRESS);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }

            TemplateExportParams params = new TemplateExportParams(
                    file.getPath());

            Map<String, Object> map = FileUtil.beanToMap(dto);
            Map disposeMap = FileUtil.nullToEmpty(map);
            Workbook wb = ExcelExportUtil.exportExcel(params, disposeMap);

            String fileName = areaStr + ".xls";

            String dir = mkdirs(basePath);
            filePath = dir + File.separator + fileName;
            keepExcel(wb, filePath);
        }
        return filePath;
    }


    public static void keepExcel(Workbook workbook, String path) {
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(path);
            workbook.write(out);
            // 刷新
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * @return void
     * @throws [response, filePaths, zipFileName]
     * @Description:    创建压缩文件，并下载
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/18 15:11
     */
    public static void mackZipAndDownload(HttpServletResponse response, List<String> filePaths,
                                          String year) {
        ZipOutputStream out = null;
        try {
            if (CollUtil.isNotEmpty(filePaths)) {
                if (filePaths.size() == 1) {
                    String filePath = filePaths.get(0);
                    String fileName = "林业产业产值" + "(" + year + ")" + "." + FILE_EXTENSION_XLS;

                    // 文件的存放路径
                    FileInputStream fis = new FileInputStream(filePath);

                    // 设置输出的格式
                    FileUtil.setResponseContentDisposition(response, fileName);

                    response.setContentType("application/octet-stream");
                    // 循环取出流中的数据
                    byte[] b = new byte[1024];
                    int len;
                    while ((len = fis.read(b)) > 0) {
                        response.getOutputStream().write(b, 0, len);
                    }
                    fis.close();
                }
                // 设置响应消息头
                String fileName = "林业产业产值" + "(" + year + ")" + ZIP_FILE_SUFFIX;
                FileUtil.setResponseContentDisposition(response, fileName);
                response.setCharacterEncoding("UTF-8");
                response.setContentType("application/zip;charset=utf-8");

                out = new ZipOutputStream(response.getOutputStream());
                out.setMethod(ZipOutputStream.DEFLATED);

                for (String filePath : filePaths) {
                    File file = new File(filePath);
                    FileInputStream fis = new FileInputStream(file);
                    // 每个文件命名
                    String zipEntryName = file.getName();
                    out.putNextEntry(new ZipEntry(zipEntryName));
                    // 写入压缩包
                    int len;
                    byte[] buffer = new byte[1024];
                    while ((len = fis.read(buffer)) > 0) {
                        out.write(buffer, 0, len);
                    }
                    out.closeEntry();
                    fis.close();
                }
                out.close();
                out.flush();
            } else {
                FileUtil.setResponseContentDisposition(response, "数据不存在");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * @return java.lang.String
     * @throws [bashPath]
     * @Description:    创建文件夹
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/18 15:02
     */
    public static String mkdirs(String bashPath) {
        Date date = new Date();
        String dateStr = DateUtil.parseDate(date, DateUtil.YYYYMMDD);

        // 文件相对路径
        String path = dateStr.substring(0, 4) + File.separator
                + dateStr.substring(0, 6) + File.separator
                + dateStr.substring(0, 8) + File.separator;

        String fileNewPath = bashPath + path;
        File dir = new File(fileNewPath);

        boolean isSuccessMakeDir = false;
        if (!dir.exists()) {
            try {
                isSuccessMakeDir = dir.mkdirs();
                if (!isSuccessMakeDir) {
                    log.error("文件创建失败：{}" + path);
                    throw new BusinessException("创建文件夹失败！");
                }
            } catch (Exception e) {
                log.error("文件创建失败：{}" + path);
                throw new BusinessException("创建文件夹失败！");
            }
        }
        return fileNewPath;
    }


    /**
     * @return java.lang.String
     * @throws [cell]
     * @Description:    将单元格内容转换为字符串
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 16:58
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING: // 字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case FORMULA: // 公式
                returnValue = cell.getCellFormula();
                break;
            case BLANK: // 空值
                break;
            case ERROR: // 故障
                break;
            default:
                break;
        }
        if (StrUtil.isNotEmpty(returnValue)) {
            returnValue = returnValue.trim();
        }

        return returnValue;
    }


    /**
     * @Description:    总产值字符串数组
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 18:33
     */
    public static String[] totalValueArray() {
        String[] totalValueArray = new String[]{
                "tatalTatalvalue",
                "fisTatalTatalvalue",
                "fisForestAllTotal",
                "fisWetlandTotal",
                "fisBreandnurAllTotal",
                "fisBreedTotal",
                "fisNurseryTotal",
                "fisAfforeTotal",
                "fisWoodandbamAllTotal",
                "fisWoodTotal",
                "fisBamTotal",
                "fisEcoforeproAllTotal",
                "fisFruitTotal",
                "fisOilfruitTotal",
                "fisTeaTotal",
                "fisDrugTotal",
                "fisFoodTotal",
                "fisForecollTotal",
                "fisFlowerTotal",
                "fisAnimaluseTotal",
                "fisSysnoforeTotal",
                "secTatalTatalval",
                "secForestAllTatal",
                "secWetalndTatal",
                "secWoodandotherproAllTotal",
                "secWoodprocessTatal",
                "secArtboardTatal",
                "secWoodmakedTatal",
                "secOtherpronakedTotal",
                "secOtherfurniTotal",
                "secOtherpaperproTotal",
                "secOthermakedTotal",
                "secOthermakedPaperTotal",
                "secOthermakedPaperproTotal",
                "secChemistryproTaotal",
                "secWoodcraftTaotal",
                "secNowoodcraftTaotal",
                "secFruitandteaTaotal",
                "secFoodandfurTaotal",
                "secDrugprocessTaotal",
                "secOtherTaotal",
                "secSysnoforeTotal",
                "thiTatalTatalvalue",
                "thiForestAllTotal",
                "thiWetlandTotal",
                "thiForeproTotal",
                "thiTourandlieTotal",
                "thiEcologyTotal",
                "thiExpertiseTotal",
                "thiPubmanageTotal",
                "thiSysnoforeTotal",
                "specBambooTotal",
                "specCameTaotal",
                "specForeecoTotal"};
        return totalValueArray;
    }

    /**
     * @Description:    增加值字符串数组
     * @Author:        wangqingsong
     * @CreateDate:     2020/10/20 18:34
     */
    public static String[] addValueArray() {
        String[] addValueArray = new String[]{"tatalAddvalue",
                "fisTatalAddvalue",
                "fisForestAllAdd",
                "fisWetlandAdd",
                "fisBreandnurAllAdd",
                "fisBreedAdd",
                "fisNurseryAdd",
                "fisAfforeAdd",
                "fisWoodandbamAllAdd",
                "fisWoodAdd",
                "fisBamAdd",
                "fisEcoforeproAllAdd",
                "fisFruitAdd",
                "fisOilfruitAdd",
                "fisTeaAllAdd",
                "fisDrugAdd",
                "fisFoodAdd",
                "fisForecollAdd",
                "fisFlowerAdd",
                "fisAnimaluseAdd",
                "fisSysnoforeAdd",
                "secTatalAddval",
                "secForestAllAdd",
                "secWetalndAdd",
                "secWoodandotherproAllAdd",
                "secWoodprocessAdd",
                "secArtboardAdd",
                "secWoodmakedAdd",
                "secOtherpronakedAdd",
                "secOtherfurniAdd",
                "secOtherpaperproAdd",
                "secOthermakedAdd",
                "secOthermakedPaperAdd",
                "secOthermakedPaperAddpro",
                "secChemistryproAdd",
                "secWoodcraftAdd",
                "secNowoodcraftAdd",
                "secFruitandteaAdd",
                "secFoodandfurAdd",
                "secDrugprocessAdd",
                "secOtherAdd",
                "secSysnoforeAdd",
                "thiTatalAddvalue",
                "thiForestAllAdd",
                "thiWetlandAdd",
                "thiForeproAdd",
                "thiTourandlieAdd",
                "thiEcologyAdd",
                "thiExpertiseAdd",
                "thiPubmanageAdd",
                "thiSysnoforeAdd",
                "specBambooAdd",
                "specCameAdd",
                "specForeecoAdd"};
        return addValueArray;
    }

    public static String mapToString(Map<String, String> map, String value) {
        for (Map.Entry<String, String> stringEntry : map.entrySet()) {
            if (stringEntry.getValue().equals(value)) {
                return stringEntry.getKey();
            }
        }
        return null;
    }

}
